/* ---------------------------------------------------------------------- */
/* Script generated with: DeZign for Databases v5.2.3                     */
/* Target DBMS:           MS SQL Server 2005                              */
/* Project file:          blue_pumpkin_diagram.dez                        */
/* Project name:                                                          */
/* Author:                                                                */
/* Script type:           Database creation script                        */
/* Created on:            2010-07-16 04:55                                */
/* Model version:         Version 2010-07-16                              */
/* ---------------------------------------------------------------------- */


/* ---------------------------------------------------------------------- */
/* Tables                                                                 */
/* ---------------------------------------------------------------------- */

/* ---------------------------------------------------------------------- */
/* Add table "Users"                                                      */
/* ---------------------------------------------------------------------- */

CREATE TABLE [Users] (
    [userid] INTEGER IDENTITY(1,1) NOT NULL,
    [username] VARCHAR(40) NOT NULL,
    [password] VARCHAR(40) CONSTRAINT [DEF_Users_password] DEFAULT 'passw0rd' NOT NULL,
    [email] VARCHAR(200) NOT NULL,
    [roleid] INTEGER,
    [createdate] DATETIME CONSTRAINT [DEF_Users_createdate] DEFAULT getdate() NOT NULL,
    [createby] VARCHAR(40),
    [status] BIT CONSTRAINT [DEF_Users_status] DEFAULT 1 NOT NULL,
    CONSTRAINT [PK_Users] PRIMARY KEY ([userid])
)
GO

/* ---------------------------------------------------------------------- */
/* Add table "UserProfiles"                                               */
/* ---------------------------------------------------------------------- */

CREATE TABLE [UserProfiles] (
    [userid] INTEGER NOT NULL,
    [fullname] VARCHAR(100) NOT NULL,
    [address] VARCHAR(500) NOT NULL,
    [phone] VARCHAR(20) NOT NULL,
    [gender] BIT NOT NULL,
    [dateofbirth] DATETIME NOT NULL,
    [image] VARCHAR(500),
    CONSTRAINT [PK_UserProfiles] PRIMARY KEY ([userid])
)
GO

/* ---------------------------------------------------------------------- */
/* Add table "M_Roles"                                                    */
/* ---------------------------------------------------------------------- */

CREATE TABLE [M_Roles] (
    [roleid] INTEGER IDENTITY(1,1) NOT NULL,
    [rolename] VARCHAR(40) NOT NULL,
    CONSTRAINT [PK_M_Roles] PRIMARY KEY ([roleid])
)
GO

/* ---------------------------------------------------------------------- */
/* Add table "FAQS"                                                       */
/* ---------------------------------------------------------------------- */

CREATE TABLE [FAQS] (
    [faqid] INTEGER IDENTITY(1,1) NOT NULL,
    [question] VARCHAR(max) NOT NULL,
    [answer] VARCHAR(max) NOT NULL,
    [createdate] DATETIME CONSTRAINT [DEF_FAQS_createdate] DEFAULT getdate() NOT NULL,
    [status] BIT CONSTRAINT [DEF_FAQS_status] DEFAULT 1 NOT NULL,
    CONSTRAINT [PK_FAQS] PRIMARY KEY ([faqid])
)
GO

/* ---------------------------------------------------------------------- */
/* Add table "Types"                                                      */
/* ---------------------------------------------------------------------- */

CREATE TABLE [Types] (
    [typeid] INTEGER IDENTITY(1,1) NOT NULL,
    [typename] VARCHAR(40) NOT NULL,
    CONSTRAINT [PK_Types] PRIMARY KEY ([typeid])
)
GO

/* ---------------------------------------------------------------------- */
/* Add table "Events"                                                     */
/* ---------------------------------------------------------------------- */

CREATE TABLE [Events] (
    [eventid] INTEGER IDENTITY(1,1) NOT NULL,
    [typeid] INTEGER,
    [eventname] VARCHAR(40) NOT NULL,
    [details] VARCHAR(max) NOT NULL,
    [createdate] DATETIME CONSTRAINT [DEF_Events_createdate] DEFAULT getdate() NOT NULL,
    [begindate] DATETIME NOT NULL,
    [enddate] DATETIME NOT NULL,
    [image] VARCHAR(500) NOT NULL,
    [status] BIT CONSTRAINT [DEF_Events_status] DEFAULT 1 NOT NULL,
    [createby] VARCHAR(40),
	[numberofwinners] int,
    CONSTRAINT [PK_Events] PRIMARY KEY ([eventid])
)
GO

/* ---------------------------------------------------------------------- */
/* Add table "Requests"                                                   */
/* ---------------------------------------------------------------------- */

CREATE TABLE [Requests] (
    [reqid] INTEGER IDENTITY(1,1) NOT NULL,
    [eventid] INTEGER,
    [userid] INTEGER,
    [status] INTEGER CONSTRAINT [DEF_Requests_status] DEFAULT 0 NOT NULL,
    [note] VARCHAR(200),
    CONSTRAINT [PK_Requests] PRIMARY KEY ([reqid])
)
GO

/* ---------------------------------------------------------------------- */
/* Add table "T_EventPrizes"                                              */
/* ---------------------------------------------------------------------- */

CREATE TABLE [T_EventPrizes] (
    [eventprizeid] INTEGER IDENTITY(1,1) NOT NULL,
    [eventid] INTEGER,
    [userid] INTEGER,
    [score] FLOAT NOT NULL,
    CONSTRAINT [PK_T_EventPrizes] PRIMARY KEY ([eventprizeid])
)
GO

/* ---------------------------------------------------------------------- */
/* Foreign key constraints                                                */
/* ---------------------------------------------------------------------- */

ALTER TABLE [Users] ADD CONSTRAINT [M_Roles_Users] 
    FOREIGN KEY ([roleid]) REFERENCES [M_Roles] ([roleid])
GO

ALTER TABLE [UserProfiles] ADD CONSTRAINT [Users_UserProfiles] 
    FOREIGN KEY ([userid]) REFERENCES [Users] ([userid])
GO

ALTER TABLE [Events] ADD CONSTRAINT [Types_Events] 
    FOREIGN KEY ([typeid]) REFERENCES [Types] ([typeid])
GO

ALTER TABLE [Requests] ADD CONSTRAINT [Users_Requests] 
    FOREIGN KEY ([userid]) REFERENCES [Users] ([userid])
GO

ALTER TABLE [Requests] ADD CONSTRAINT [Events_Requests] 
    FOREIGN KEY ([eventid]) REFERENCES [Events] ([eventid])
GO

ALTER TABLE [T_EventPrizes] ADD CONSTRAINT [Users_T_EventPrizes] 
    FOREIGN KEY ([userid]) REFERENCES [Users] ([userid])
GO

ALTER TABLE [T_EventPrizes] ADD CONSTRAINT [Events_T_EventPrizes] 
    FOREIGN KEY ([eventid]) REFERENCES [Events] ([eventid])
GO
